Oracle 19c 新特性：混合分区表Hybrid partitioned tables强体验
老张拉呱：thomas zhang，甲骨文云平台事业部资深技术顾问，2008年加入甲骨文公司数据库咨询部门，10+年甲骨文解决方案咨询支持经验，资深系统工程师、Oracle OCM认证专家，具有丰富的Cloud /IT项目经验。目前主要负责甲骨文中国北方区（医院/卫生、交通、制造、教育、政府、证券、媒体、金融、零售等行业）客户的数据库、中间件、IaaS/PaaS、集成系统等相关技术解决方案咨询工作。
签名：我为人人，人人为我，三人行，必有我师。
新浪微博： http://weibo.com/tomszrp
从Oracle Database 19c开始，Oracle数据库支持Hybridpartitioned tables，也就是混合分区表，进一步扩展了Oracle分区技术。这里的混合指的是数据的分布，一些分区可以位于数据库中，另一些可以是位于数据库外部的文件（比如操作系统文件或Hadoop Distributed File System (HDFS) 文件）。这个特性的出现，其实一点也不奇怪，因为从12.2开始就支持了外部表分区、只读分区，在19c中只是将内部分区（internal partitions）与Oracle外部分区（external partitions）特性结合起来，形成一个更通用的分区，称为混合分区表。
混合分区的现实意义还是比较明显的，首先通过混合分区可以轻松地将内部（驻留在Oracle表空间中--internal partitions）和外部的数据（external partitions）集成到单个分区表中，其次可以方便地将非活跃数据移动到外部文件，在而降低存储成本的同时也更加方便数据交换。
1）混合分区表支持外部分区的所有现有外部表类型:
•ORACLE_DATAPUMP
•ORACLE_LOADER
•ORACLE_HDFS
•ORACLE_HIVE
2）所有外部表参数均适用于混合分区表的外部分区。
3）混合分区表可以跨内部、外部分区使用基于分区的优化技术，典型的比如：静态分区修剪、动态分区修剪、布隆修剪
4）混合分区表支持的操作
（1）当前仅支持创建single-level的range和list分区，其中只有single-level LIST分区支持HIVE
（2）可以使用alter table ...DDLs操作，比如ADD,DROP和RENAME partitions
（3）可以在分区级别修改external partitions的external data sources 位置
（4）可以将既有的内部分区表修改为混合分区表
（5）可以修改现有的location到empty location形成一个空的external partition
（6）可以针对内部分区创建global partial non-unique indexes
（7）可以针对内部分区创建materialized views
(8) 可以创建包含外部分区的materialized views，前提是QUERY_REWRITE_INTEGRITY必须为STALE_TOLERATED模式
（9）DML操作只能针对混合分区表的内部分区
(10) Validatingwith ANALYZE TABLE ... VALIDATE STRUCTURE on internal partitions only on hybridpartitioned tables
(11)Alteringan existing hybrid partitioned table with no external partitions to apartitioned table with internal partitions only（12）Anexternal partition can be exchanged with an external nonpartitioned table. Alsoan internal partition can be exchanged with an internal nonpartitioned table.
注意：1）不支持存储在外部分区中的数据强制约束，例如不能在混合分区表上强制主键或外键约束。在混合分区表上，只支持RELYDISABLE约束，要使用基于这种约束的优化特性，需要配合会话参数QUERY_REWRITE_INTEGRITY（设置为TRUSTED或STALE_TOLERATED）。
2）在混合分区表级别定义的AutomaticData Optimization (ADO)策略只影响内部分区
◊Hybrid partitioned tables-混合分区表的限制
（1）Restrictionsthat apply to external tables also apply to hybrid partitioned tables unlessexplicitly noted
（2）不支持REFERENCE和SYSTEM分区方法
（3）No uniqueindexes or global unique indexes. Only partial indexes are allowed and uniqueindexes cannot be partial.
（4）Attributeclustering (CLUSTERING clause) is not allowed
（5）DMLoperations only on internal partitions of a hybrid partitioned table (externalpartitions are treated as read-only partitions)
（6）In-memorydefined on the table level only has an effect on internal partitions of thehybrid partitioned table
（7）No columndefault value
（8）Invisiblecolumns are not allowed
（9）TheCELLMEMORY clause is not allowed
（10）SPLIT,MERGE, and MOVE maintenance operations are not allowed on internal partitions
（11）不支持LOB, LONG和ADT类型
（12）只允许RELYconstraints
◊Hybrid partitioned tables-混合分区表初体验
1）准备测试数据
sale_2016.txt
region,time_id,amount
EAST,20160101,6000
EAST,20160102,3000
EAST,20160103,9012
EAST,20160104,2450
EAST,20160105,6709
SOUTH,20160101,4000
SOUTH,20160102,2120
SOUTH,20160103,6300
SOUTH,20160104,3850
SOUTH,20160105,2090
WEST,20160101,2467
WEST,20160102,2140
WEST,20160103,5300
WEST,20160104,2470
WEST,20160105,4080
NORTH,20160101,2600
NORTH,20160102,1300
NORTH,20160103,1250
NORTH,20160104,4350
NORTH,20160105,3190
sale_2017.txt
region,time_id,amount
EAST,20170101,8000
EAST,20170102,7000
EAST,20170103,6500
EAST,20170104,3450
EAST,20170105,9000
SOUTH,20170101,2000
SOUTH,20170102,3120
SOUTH,20170103,2300
SOUTH,20170104,5850
SOUTH,20170105,1900
WEST,20170101,3400
WEST,20170102,2400
WEST,20170103,5900
WEST,20170104,5450
WEST,20170105,1780
NORTH,20170101,2000
NORTH,20170102,1000
NORTH,20170103,3000
NORTH,20170104,2350
NORTH,20170105,2190
sale_2018.txt
region,time_id,amount
EAST,20180101,2100
EAST,20180102,7800
EAST,20180103,6900
EAST,20180104,9450
EAST,20180105,9700
SOUTH,20180101,1300
SOUTH,20180102,2120
SOUTH,20180103,6300
SOUTH,20180104,2850
SOUTH,20180105,7900
WEST,20180101,3800
WEST,20180102,2600
WEST,20180103,5200
WEST,20180104,5250
WEST,20180105,2980
NORTH,20180101,2120
NORTH,20180102,1230
NORTH,20180103,3500
NORTH,20180104,2050
NORTH,20180105,1060
sale_2018.sql
insert into hybrid_test values('EAST', to_date('20190101','yyyy-mm-dd'),1032);
insert into hybrid_test values('EAST', to_date('20190102','yyyy-mm-dd'),2371);
commit;
2）定义DIRECTORY
CDB$ROOT@SYS>conn zrp/zrp@pdb1
PDB1@ZRP>CREATEDIRECTORY sales_data as '/u01/app/oracle/oradata/extfiles';
PDB1@ZRP>CREATEDIRECTORY sales_data_2016 as '/u01/app/oracle/oradata/extfiles/2016';
PDB1@ZRP>CREATEDIRECTORY sales_data_2017 as '/u01/app/oracle/oradata/extfiles/2017';
3）创建Hybridpartitioned tables-混合分区表
PDB1@ZRP>CREATE TABLE hybrid_test
2 (
3 region varchar2(6) NOT NULL,
4 time_id DATE NOT NULL,
5 amount NUMBER(10,2)
6 )
7 EXTERNAL PARTITION ATTRIBUTES --必须加这个子句声明
8 (
9 TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY sales_data
11 ACCESS PARAMETERS( FIELDS TERMINATED BY ','
12 (region,time_id DATE 'yyyy-mm-dd',amount)
13 )
14 REJECT LIMIT UNLIMITED
15 )
16 PARTITION BY RANGE (time_id)
17 ( PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')) EXTERNAL, --空的外部分区
18 PARTITION sales_2016 VALUES LESS THAN (TO_DATE('2017-01-01','yyyy-mm-dd'))
19 EXTERNAL DEFAULT DIRECTORY sales_data_2016 LOCATION ('sales_2016.txt'),
20 PARTITION sales_2017 VALUES LESS THAN (TO_DATE('2018-01-01','yyyy-mm-dd'))
21 EXTERNAL DEFAULT DIRECTORY sales_data_2017 LOCATION ('sales_2017.txt'),
22 PARTITION sales_2018 VALUES LESS THAN (TO_DATE('2019-01-01','yyyy-mm-dd')) EXTERNAL LOCATION ('sales_2018.txt'),
23 PARTITION sales_2019 VALUES LESS THAN (TO_DATE('2020-01-01','yyyy-mm-dd')) --内部分区
24 );
Table created.
##通过dba_tables数据字典的hybrid字段可以看出是否是混合分区
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner='ZRP' and table_name='HYBRID_TEST';
TABLE_NAME PAR HYB
-------------------- --- ---
HYBRID_TEST YES YES
PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';
PDB1@ZRP>
##插入几行数据（只能插入到内部分区（internal partitions），我这里是sales_2019).
PDB1@ZRP>select * from hybrid_test partition (sales_2015);
no rows selected
PDB1@ZRP>insert into hybrid_test values('EAST', to_date('20190101','yyyy-mm-dd'),1032);
1 row created.
PDB1@ZRP>insert into hybrid_test values('EAST', to_date('20190102','yyyy-mm-dd'),2371);
1 row created.
PDB1@ZRP>commit;
Commit complete.
PDB1@ZRP>
##查看各分区的数据
PDB1@ZRP>select * from hybrid_test partition (sales_2019);
REGION TIME_ID AMOUNT
------ ------------------- ----------
EAST 2019-01-01 00:00:00 1032
EAST 2019-01-02 00:00:00 2371
PDB1@ZRP>select * from hybrid_test partition (sales_2015);
no rows selected
PDB1@ZRP>select * from hybrid_test partition (sales_2016);
REGION TIME_ID AMOUNT
------ ------------------- ----------
EAST 2016-01-01 00:00:00 6000
EAST 2016-01-02 00:00:00 3000
EAST 2016-01-03 00:00:00 9012
EAST 2016-01-04 00:00:00 2450
EAST 2016-01-05 00:00:00 6709
SOUTH 2016-01-01 00:00:00 4000
SOUTH 2016-01-02 00:00:00 2120
SOUTH 2016-01-03 00:00:00 6300
SOUTH 2016-01-04 00:00:00 3850
SOUTH 2016-01-05 00:00:00 2090
WEST 2016-01-01 00:00:00 2467
WEST 2016-01-02 00:00:00 2140
WEST 2016-01-03 00:00:00 5300
WEST 2016-01-04 00:00:00 2470
WEST 2016-01-05 00:00:00 4080
NORTH 2016-01-01 00:00:00 2600
NORTH 2016-01-02 00:00:00 1300
NORTH 2016-01-03 00:00:00 1250
NORTH 2016-01-04 00:00:00 4350
NORTH 2016-01-05 00:00:00 3190
20 rows selected.
PDB1@ZRP>select * from hybrid_test partition (sales_2017);
REGION TIME_ID AMOUNT
------ ------------------- ----------
EAST 2017-01-01 00:00:00 8000
EAST 2017-01-02 00:00:00 7000
EAST 2017-01-03 00:00:00 6500
EAST 2017-01-04 00:00:00 3450
EAST 2017-01-05 00:00:00 9000
SOUTH 2017-01-01 00:00:00 2000
SOUTH 2017-01-02 00:00:00 3120
SOUTH 2017-01-03 00:00:00 2300
SOUTH 2017-01-04 00:00:00 5850
SOUTH 2017-01-05 00:00:00 1900
WEST 2017-01-01 00:00:00 3400
WEST 2017-01-02 00:00:00 2400
WEST 2017-01-03 00:00:00 5900
WEST 2017-01-04 00:00:00 5450
WEST 2017-01-05 00:00:00 1780
NORTH 2017-01-01 00:00:00 2000
NORTH 2017-01-02 00:00:00 1000
NORTH 2017-01-03 00:00:00 3000
NORTH 2017-01-04 00:00:00 2350
NORTH 2017-01-05 00:00:00 2190
20 rows selected.
PDB1@ZRP>select * from hybrid_test partition (sales_2018);
REGION TIME_ID AMOUNT
------ ------------------- ----------
EAST 2018-01-01 00:00:00 2100
EAST 2018-01-02 00:00:00 7800
EAST 2018-01-03 00:00:00 6900
EAST 2018-01-04 00:00:00 9450
EAST 2018-01-05 00:00:00 9700
SOUTH 2018-01-01 00:00:00 1300
SOUTH 2018-01-02 00:00:00 2120
SOUTH 2018-01-03 00:00:00 6300
SOUTH 2018-01-04 00:00:00 2850
SOUTH 2018-01-05 00:00:00 7900
WEST 2018-01-01 00:00:00 3800
WEST 2018-01-02 00:00:00 2600
WEST 2018-01-03 00:00:00 5200
WEST 2018-01-04 00:00:00 5250
WEST 2018-01-05 00:00:00 2980
NORTH 2018-01-01 00:00:00 2120
NORTH 2018-01-02 00:00:00 1230
NORTH 2018-01-03 00:00:00 3500
NORTH 2018-01-04 00:00:00 2050
NORTH 2018-01-05 00:00:00 1060
20 rows selected.
PDB1@ZRP>select /*+ gather_plan_statistics */ * from hybrid_test where time_id=to_date('20160102','yyyymmdd');
REGION TIME_ID AMOUNT
------ ------------------- ----------
EAST 2016-01-02 00:00:00 3000
SOUTH 2016-01-02 00:00:00 2120
WEST 2016-01-02 00:00:00 2140
NORTH 2016-01-02 00:00:00 1300
PDB1@ZRP>select * from table(dbms_xplan.display_cursor(format=>'IOSTATS PARTITION LAST')) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 71z2djd7chmxh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from hybrid_test where
time_id=to_date('20160102','yyyymmdd')
Plan hash value: 2383463387
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((SYS_OP_XTNN("HYBRID_TEST"."TIME_ID","HYBRID_TEST"."REGION") AND "TIME_ID"=TO_DATE('
2016-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
21 rows selected.
PDB1@ZRP>
4）将混合分区表转为Internal Partitioned Tables（传统分区）
1）第一步删除external partitions
2）第二步删除external partition attributes
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner='ZRP' and table_name='HYBRID_TEST';
TABLE_NAME PAR HYB
-------------------- --- ---
HYBRID_TEST YES YES
PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ
-------------------- -------------------- ------------------------------ ------- ----
HYBRID_TEST SALES_2015 SYSTEM NO YES
HYBRID_TEST SALES_2016 SYSTEM NO YES
HYBRID_TEST SALES_2017 SYSTEM NO YES
HYBRID_TEST SALES_2018 SYSTEM NO YES
HYBRID_TEST SALES_2019 USERS YES NO
PDB1@ZRP>
然后执行下面删除分区语句
alter table hybrid_test drop partition sales_2015;
alter table hybrid_test drop partition sales_2016;
alter table hybrid_test drop partition sales_2017;
alter table hybrid_test drop partition sales_2018;
alter table hybrid_test drop EXTERNAL PARTITION ATTRIBUTES();
再查看数据字典
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner='ZRP' and table_name='HYBRID_TEST';
TABLE_NAME PAR HYB
-------------------- --- ---
HYBRID_TEST YES NO
PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ
-------------------- -------------------- ------------------------------ ------- ----
HYBRID_TEST SALES_2019 USERS YES NO
PDB1@ZRP>
已经转换为传统分区表
5）将Internal Partitioned Tables（传统分区）转为混合分区表
1）首先要增加EXTERNAL PARTITION ATTRIBUTES
2）第二步增加external partitions
注：至少要有一个internal partition
PDB1@ZRP>alter table hybrid_test
2 ADD EXTERNAL PARTITION ATTRIBUTES
3 ( TYPE ORACLE_LOADER
4 DEFAULT DIRECTORY sales_data
5 ACCESS PARAMETERS ( FIELDS TERMINATED BY ','
6 (region,time_id DATE 'yyyy-mm-dd',amount)
7 )
8 REJECT LIMIT UNLIMITED
9 );
Table altered.
PDB1@ZRP>
然后将之前的测试外部数据作为外部分区添加进去
PDB1@ZRP>alter table hybrid_test
2 ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')) EXTERNAL;
ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')) EXTERNAL
*
ERROR at line 2:
ORA-14074: partition bound must collate higher than that of the last partition
发现不行，因为我不想破坏数据，顺道练习了个split和exchange动作
##将hybrid_test的分区sales_2019拆分
alter table hybrid_test
split partition sales_2019 into
(partition sales_2014 VALUES LESS THAN (TO_DATE('2015-01-01','yyyy-mm-dd')),
partition sales_2019
);
##创建一个中间表
create table hybrid_test_temp
(
region varchar2(6) NOT NULL,
time_id DATE NOT NULL,
amount NUMBER(10,2)
);
##把里面的数据交换出去
alter table hybrid_test exchange partition sales_2019 with table hybrid_test_temp;
##然后删除这个sales_2019
alter table hybrid_test drop partition sales_2019;
##接下来就可以添加外部分区了
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')) EXTERNAL;
Table altered.
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2016 VALUES LESS THAN (TO_DATE('2017-01-01','yyyy-mm-dd'))
EXTERNAL DEFAULT DIRECTORY sales_data_2016 LOCATION ('sales_2016.txt');
Table altered.
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2017 VALUES LESS THAN (TO_DATE('2018-01-01','yyyy-mm-dd'))
EXTERNAL DEFAULT DIRECTORY sales_data_2017 LOCATION ('sales_2017.txt');
Table altered.
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2018 VALUES LESS THAN (TO_DATE('2019-01-01','yyyy-mm-dd'))
EXTERNAL LOCATION ('sales_2018.txt');
Table altered.
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2019 VALUES LESS THAN (TO_DATE('2020-01-01','yyyy-mm-dd'));
Table altered.
#最后再把刚才交换出去的数据交换回来，这样就恢复原样了
PDB1@ZRP>alter table hybrid_test exchange partition sales_2019 with table hybrid_test_temp;
Table altered.
PDB1@ZRP>select * from hybrid_test partition (sales_2019);
REGION TIME_ID AMOUNT
------ ------------------- ----------
EAST 2019-01-01 00:00:00 1032
EAST 2019-01-02 00:00:00 2371
##把这个临时过渡分区删除
PDB1@ZRP>alter table hybrid_test drop partition sales_2014;
Table altered.
这样就又恢复到最初的混合分区的样子了
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner='ZRP' and table_name='HYBRID_TEST';
TABLE_NAME PAR HYB
-------------------- --- ---
HYBRID_TEST YES YES
PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ
-------------------- -------------------- ------------------------------ ------- ----
HYBRID_TEST SALES_2015 USERS NO YES
HYBRID_TEST SALES_2016 USERS NO YES
HYBRID_TEST SALES_2017 USERS NO YES
HYBRID_TEST SALES_2018 USERS NO YES
HYBRID_TEST SALES_2019 USERS YES NO
PDB1@ZRP>select * from hybrid_test partition(sales_2016);
REGION TIME_ID AMOUNT
------ ------------------- ----------
EAST 2016-01-01 00:00:00 6000
EAST 2016-01-02 00:00:00 3000
EAST 2016-01-03 00:00:00 9012
EAST 2016-01-04 00:00:00 2450
EAST 2016-01-05 00:00:00 6709
SOUTH 2016-01-01 00:00:00 4000
SOUTH 2016-01-02 00:00:00 2120
SOUTH 2016-01-03 00:00:00 6300
SOUTH 2016-01-04 00:00:00 3850
SOUTH 2016-01-05 00:00:00 2090
WEST 2016-01-01 00:00:00 2467
WEST 2016-01-02 00:00:00 2140
WEST 2016-01-03 00:00:00 5300
WEST 2016-01-04 00:00:00 2470
WEST 2016-01-05 00:00:00 4080
NORTH 2016-01-01 00:00:00 2600
NORTH 2016-01-02 00:00:00 1300
NORTH 2016-01-03 00:00:00 1250
NORTH 2016-01-04 00:00:00 4350
NORTH 2016-01-05 00:00:00 3190
20 rows selected.
PDB1@ZRP>
原创：老张拉呱